﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace GgcmsTBK
{
     public  class ExcelHelper
    {
        /// <summary>
        /// 获取Excel文件数据表列表
        /// </summary>
        public static List<string> GetExcelTables(string ExcelFileName)
        {
            DataTable dt = new DataTable();
            List<string> TablesList = new List<string>();
            if (File.Exists(ExcelFileName))
            {
                using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" + ExcelFileName))
                {
                    try
                    {
                        conn.Open();
                        dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                    }
                    catch (Exception exp)
                    {
                        throw exp;
                    }

                    //获取数据表个数
                    int tablecount = dt.Rows.Count;
                    for (int i = 0; i < tablecount; i++)
                    {
                        string tablename = dt.Rows[i][2].ToString().Trim().TrimEnd('$');
                        if (TablesList.IndexOf(tablename) < 0)
                        {
                            TablesList.Add(tablename);
                        }
                    }
                }
            }
            return TablesList;
        }
        /// <summary>
        /// 将Excel文件导出至DataTable(第一行作为表头)
        /// </summary>
        /// <param name="ExcelFilePath">Excel文件路径</param>
        /// <param name="TableName">数据表名，如果数据表名错误，默认为第一个数据表名</param>
        public static DataTable InputFromExcel(string ExcelFilePath, string TableName)
        {
            if (!File.Exists(ExcelFilePath))
            {
                throw new Exception("Excel文件不存在！");
            }

            //如果数据表名不存在，则数据表名为Excel文件的第一个数据表

            List<string> TableList = new List<string>();
            TableList = GetExcelTables(ExcelFilePath);
            if (TableList.IndexOf(TableName) < 0)
            {
                TableName = TableList[0].ToString().Trim();
            }

            DataTable table = new DataTable();
            OleDbConnection dbcon = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFilePath + ";Extended Properties=Excel 8.0");
            OleDbCommand cmd = new OleDbCommand("select * from [" + TableName + "$]", dbcon);
            OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);

            try
            {
                if (dbcon.State == ConnectionState.Closed)
                {
                    dbcon.Open();
                }
                adapter.Fill(table);
            }
            catch (Exception exp)
            {
                throw exp;
            }
            finally
            {
                if (dbcon.State == ConnectionState.Open)
                {
                    dbcon.Close();
                }
            }
            return table;
        }



        public static DataTable InputFromCSV(string fp)
        {
            string csv = File.ReadAllText(fp, Encoding.Default);
            string[] rows = csv.Split("\r\n".ToArray());
            DataTable dt = new DataTable("table");
            bool isfirst = true;
            foreach (string r in rows)
            {
                if (string.IsNullOrWhiteSpace(r))
                {
                    continue;
                }
                string tmp = r;
                Regex regex = new Regex("\\\".+\\\"");
                var cc = regex.Matches(r);
                foreach (Match m in cc)
                {
                    string q = m.Value.Replace("\"","").Replace(","," ");
                    tmp = tmp.Replace(m.Value, q);
                }
                string[] cols = tmp.Split(',');

                if (isfirst)
                {
                    foreach (string c in cols)
                    {
                        dt.Columns.Add(c, typeof(string));
                    }
                    isfirst = false;
                }
                else
                {
                    try
                    {
                        DataRow row = dt.NewRow();
                        for (int i = 0; i < cols.Length; i++)
                        {

                            row[i] = cols[i];

                        }
                        dt.Rows.Add(row);
                    }
                    catch (Exception ex)
                    {
                        
                    }

                }
            }
            return dt;
        }
        /// <summary>
        /// 将数据导出至Excel文件
        /// </summary>
        /// <param name="Table">DataTable对象</param>
        /// <param name="ExcelFilePath">Excel文件路径</param>
        public static bool OutputToExcel(DataTable Table, string ExcelFilePath)
        {
            if (File.Exists(ExcelFilePath))
            {
                File.Delete(ExcelFilePath);
            }

            if (string.IsNullOrEmpty(Table.TableName))
            {
                Table.TableName = "Sheet1";
            }

            //数据表的列数
            int ColCount = Table.Columns.Count;

            //用于记数，实例化参数时的序号
            int i = 0;

            //创建参数
            OleDbParameter[] para = new OleDbParameter[ColCount];

            //创建表结构的SQL语句
            string TableStructStr = @"Create Table " + Table.TableName + "(";

            //连接字符串
            string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFilePath + ";Extended Properties=Excel 8.0;";
            OleDbConnection objConn = new OleDbConnection(connString);

            //创建表结构
            OleDbCommand objCmd = new OleDbCommand();

            //数据类型集合
            List<string> DataTypeList = new List<string>();
            DataTypeList.Add("System.Decimal");
            DataTypeList.Add("System.Double");
            DataTypeList.Add("System.Int16");
            DataTypeList.Add("System.Int32");
            DataTypeList.Add("System.Int64");
            DataTypeList.Add("System.Single");

            //遍历数据表的所有列，用于创建表结构
            foreach (DataColumn col in Table.Columns)
            {
                //如果列属于数字列，则设置该列的数据类型为double
                string colname = col.ColumnName;
                if (!string.IsNullOrEmpty(col.Caption))
                {
                    colname = col.Caption;
                }
                if (DataTypeList.IndexOf(col.DataType.ToString()) >= 0)
                {
                    para[i] = new OleDbParameter("@" + colname, OleDbType.Double);
                    objCmd.Parameters.Add(para[i]);

                    //如果是最后一列
                    if (i + 1 == ColCount)
                    {
                        TableStructStr += "[" + colname + "]" + " double)";
                    }
                    else
                    {
                        TableStructStr += "[" + colname + "]" + " double,";
                    }
                }
                else
                {
                    para[i] = new OleDbParameter("@" + colname, OleDbType.LongVarChar);
                    objCmd.Parameters.Add(para[i]);

                    //如果是最后一列
                    if (i + 1 == ColCount)
                    {
                        TableStructStr += "[" + colname + "]" + " text)";
                    }
                    else
                    {
                        TableStructStr += "[" + colname + "]" + " text,";
                    }
                }
                i++;
            }

            //创建Excel文件及文件结构
            try
            {
                objCmd.Connection = objConn;
                objCmd.CommandText = TableStructStr;

                if (objConn.State == ConnectionState.Closed)
                {
                    objConn.Open();
                }
                objCmd.ExecuteNonQuery();
            }
            catch (Exception exp)
            {
                throw exp;
            }

            //插入记录的SQL语句
            string InsertSql_1 = "Insert into " + Table.TableName + " (";
            string InsertSql_2 = " Values (";
            string InsertSql = "";

            //遍历所有列，用于插入记录，在此创建插入记录的SQL语句
            for (int colID = 0; colID < ColCount; colID++)
            {
                string colname = Table.Columns[colID].ColumnName;
                if (!string.IsNullOrEmpty(Table.Columns[colID].Caption))
                {
                    colname = Table.Columns[colID].Caption;
                }
                if (colID + 1 == ColCount)  //最后一列
                {
                    InsertSql_1 += "[" + colname + "])";
                    InsertSql_2 += "@" + colname + ")";
                }
                else
                {
                    InsertSql_1 += "["+ colname + "],";
                    InsertSql_2 += "@" + colname + ",";
                }
            }

            InsertSql = InsertSql_1 + InsertSql_2;

            //遍历数据表的所有数据行
            foreach (DataRowView rv in Table.DefaultView)
            {
                DataRow row = rv.Row;
                for (int colID = 0; colID < ColCount; colID++)
                {
                    if (para[colID].DbType == DbType.Double && row[colID].ToString().Trim() == "")
                    {
                        para[colID].Value = 0;
                    }
                    else
                    {
                        para[colID].Value = row[colID].ToString().Trim();
                    }
                }
                try
                {
                    objCmd.CommandText = InsertSql;
                    objCmd.ExecuteNonQuery();
                }
                catch (Exception exp)
                {
                    string str = exp.Message;
                }
            }
            try
            {
                if (objConn.State == ConnectionState.Open)
                {
                    objConn.Close();
                }
            }
            catch (Exception exp)
            {
                throw exp;
            }
            return true;
        }
    }
}
